import datetime
from rest_framework.views import APIView
from rest_framework.response import Response
from django.db import connection
from .. import models
from dvadmin.system.util.optimize_db_method import *

def get_id_all_record_years(id):
    '''
    获取某个id所有爬取过的的年份
    '''
    years_exist = []
    sql = f"select year from agent_record where agent_id = {id}"
    with connection.cursor() as cursor:
        cursor.execute(sql)
        for row in cursor:
            year = row[0]
            if isinstance(year, int):
                years_exist.append(year)
    return years_exist

def get_id_journal_name(id):
    with connection.cursor() as cursor:
        # 查询期刊名称 --- 因为document_{year}表里没有类似agent_id字段来区分不同journal
        query_agent = f"SELECT journal FROM agent WHERE id = {id};"
        cursor.execute(query_agent)
        journal_name = cursor.fetchone()[0]
        if journal_name is None:
            return None
        return journal_name

def distinguish_years(years, years_exist):
    years_list = []
    not_years = []
    # 检验years参数
    if not years:
        years_list = years_exist
        msg = "(请求时无years参数输入，返回有记录的全部年份数据)"
    else:
        msg = ""
        if isinstance(years, list):
            # 检验获取的years列表中所有year是否是合法年份（只需通过判断是否在years_exist中既可）
            for year_ in years:
                if year_ in years_exist:
                    years_list.append(year_)
                else:
                    not_years.append(year_)
        elif isinstance(years, int):
            years_list.append(years)
        else:
            return Response(data={"error": "The 'years' parameter must be a list or an integer"}, status=400)

    years_list.sort()  # 升序排序一下, 嘻嘻
    return msg, years_list, not_years

def check_id(id):
    if id is None:
        return Response(data={"error": "The 'id' parameter is required"},status = 400)
    else:
        try:
            int(id)
        except ValueError:
            return Response({"error": "Wrong type for 'id' parameter"}, status=400)

def get_appropriate_limit(limit_param, default_limit):
    # limit这个参数主要是用来筛去低于这个值的数据来保证向前端返回的数据量不会太大
    if not limit_param:  # 如果 limit 未提供或为空
        limit = default_limit
    else:
        try:
            limit = int(limit_param)
        except ValueError:  # 处理 limit 不是有效整数的情况
            limit = default_limit
    return limit

class JournalNumberPostView(APIView):
    """
    1. 从数据库中返回某期刊按年排序的发文量数据---json
    --Xwang
    """
    def get(self,request):
        params = request.query_params
        id = params.get('id')

        # 参数判断
        check_id(id)

        # 使用django自带的orm进行查询 该期刊所有年份数据
        rows = models.AgentRecord.objects.filter(agent_id=id).values_list('year','last_query_num')

        # 创建一个空字典，用于存储数据
        data = {}
        if rows is not None:
            for year, query_num in rows:
                if query_num != -1:
                    query_num = abs(query_num)
                if year not in data:
                    data[year] = query_num
                else:
                    data[year] += query_num
        sorted_data = dict(sorted(data.items()))

        return Response(data={
            "code": 2000,
            "id": id,
            "msg": f"获取成功，返回第{id}个期刊的发文量数据",
            "data": sorted_data
        }, status=200, content_type="application/json")

class SubjectNumView(APIView):
    """
    2. 获取特定年份或特定时间段某期刊中各个学科的发文量
    --Xwang
    """
    def post(self,request):
        data = request.data
        id = data.get('id')
        years = data.get('years')

        # 参数判断
        check_id(id)

        # 获取所有该期刊的记录年份
        years_exist = get_id_all_record_years(id)

        # 把years参数进行拆分，分为数据库中有的年份和没有的年份，下文进行区分处理
        msg, years_list,  not_years= distinguish_years(years, years_exist)

        # 创建一个空字典，用于存储数据
        result_dict = {}

        # 根据id获取期刊名
        journal_name = get_id_journal_name(id)

        for year in years_list:
            table_name = f"document_{year}"
            query = f'SELECT research_areas FROM {table_name} WHERE source_title = "{journal_name}";'
            sub_num = {}

            try:
                with connection.cursor() as cursor:
                    cursor.execute(query)

                    # 直接从表中提取research_areas字段进行分解后(因为一个值可能包含多个学科)作为键
                    for row in cursor:
                        research_areas = row[0].split(';')

                        for research_area in research_areas:
                            research_area = research_area.strip()  # 去空格
                            if research_area:
                                # 有则+1，无则为1
                                sub_num[research_area] = sub_num.get(research_area, 0) + 1
                    if not sub_num:
                        sub_num = 0
                    else:
                        # 按值降序排列
                        sub_num = dict(sorted(sub_num.items(), key=lambda item: item[1], reverse=True))

                result_dict[year] = sub_num

            except Exception as e:
                return Response({"error": f"数据库查询语句执行出错: {str(e)}"}, status=500)

        for year in not_years:
            result_dict[year] = {}
        result_dict = dict(sorted(result_dict.items()))

        return Response(data={
            "code": 2000,
            "id": id,
            "journal_name": journal_name,
            "msg": f"查询成功，返回数据年份：{years_list+not_years}{msg}",
            "data": result_dict
        }, status=200, content_type="application/json")

class InstitutionArticleNumView(APIView):
    """
    3. 特定年份或特定时间段内 某id对应的期刊中 作者 所属各个机构 发文量
    --Xwang
    """
    def post(self,request):
        data = request.data
        id = data.get('id')
        years = data.get('years')

        # 参数判断
        if not id:
            return Response(data={"error": "The 'id' parameter is required"}, status=400)

        if not isinstance(id, int):
            return Response(data={"error": "The 'id' parameter must be an integer"}, status=400)

        # 获取所有该期刊的记录年份
        years_exist = get_id_all_record_years(id)

        # 把years参数进行拆分，分为数据库中有的年份和没有的年份，下文进行区分处理
        msg, years_list,  not_years = distinguish_years(years, years_exist)

        # 创建一个空字典，用于存储数据
        result_dict = {}

        try:
            # 查询期刊名称
            journal_name = get_id_journal_name(id)

            with connection.cursor() as cursor:
                # 遍历每一年的数据
                for year in years_list:
                    table_name = f"document_{year}"
                    query = f'SELECT affiliations FROM {table_name} WHERE source_title = "{journal_name}";'

                    institution_num = {}
                    cursor.execute(query)
                    for row in cursor:
                        institutions = row[0].split(";")
                        for institution in institutions:
                            institution = institution.strip()
                            if institution:
                                institution_num[institution] = institution_num.get(institution, 0) + 1
                    if not institution_num:
                        institution_num = 0
                    else:
                        # 按值降序排列
                        institution_num = dict(sorted(institution_num.items(), key=lambda item: item[1], reverse=True))

                    # 把获得的每年数据写入结果字典用来存储，最后返回
                    result_dict[year] = institution_num

        except Exception as e:
            return Response({"error": f"数据库查询语句执行出错: {str(e)}"}, status=502)

        for year in not_years:
            result_dict[year] = {}
        result_dict = dict(sorted(result_dict.items()))

        return Response(data={
            "code": 2000,
            "id": id,
            "journal_name": journal_name,
            "msg": f"查询成功，返回数据年份：{years_list+not_years}{msg}",
            "data": result_dict
        }, status=200, content_type="application/json")

class AuthorArticleNumView(APIView):
    """
    4. 特定年份或特定时间段某个期刊中所有文章对应作者的发文量
    --Xwang
    """
    def post(self,request):
        data = request.data
        id = data.get('id')
        years = data.get('years')
        limit_param = data.get('limit')

        # 参数判断
        check_id(id)

        # 获取所有该期刊的记录年份
        years_exist = get_id_all_record_years(id)

        # 把years参数进行拆分，分为数据库中有的年份和没有的年份，下文进行区分处理
        msg, years_list,  not_years= distinguish_years(years, years_exist)

        # 获取limit参数
        limit = get_appropriate_limit(limit_param, default_limit=0)

        # 创建一个空字典，用于存储数据
        result_dict = {}

        try:
            with connection.cursor() as cursor:
                # 查询期刊名称
                journal_name = get_id_journal_name(id)

                for year in years_list:
                    author_num = {}
                    table_name = f"document_{year}"
                    query=f'SELECT author_full_names FROM {table_name} WHERE source_title = "{journal_name}";'

                    cursor.execute(query)
                    for row in cursor:
                        authors = row[0].split(';')
                        for author in authors:
                            author = author.strip()
                            author_num[author] = author_num.get(author, 0) + 1

                    # 移除发文量小于 limit 的作者
                    author_num = {author: count for author, count in author_num.items() if count > limit}

                    # 按值降序排列
                    author_num = dict(sorted(author_num.items(), key=lambda item: item[1], reverse=True))

                    result_dict[year] = author_num

        except Exception as e:
            return Response({"error": f"数据库查询语句执行出错: {str(e)}"}, status=500)

        for year in not_years:
            result_dict[year] = {}
        result_dict = dict(sorted(result_dict.items()))

        return Response(data={
            "code": 2000,
            "id": id,
            "journal_name": journal_name,
            "msg": f"查询成功，返回数据年份：{years_list+not_years}{msg}",
            "data": result_dict
        }, status=200, content_type="application/json")

class JournalArticleThemesView(APIView):
    """
    5. 特定年份或特定时间段的期刊主题
    --Xwang
    """
    def post(self,request):
        data = request.data
        id = data.get('id')
        years = data.get('years')
        limit_param = data.get('limit')

        # 参数判断
        check_id(id)

        # 获取所有该期刊的记录年份
        years_exist = get_id_all_record_years(id)

        # 把years参数进行拆分，分为数据库中有的年份和没有的年份，下文进行区分处理
        msg, years_list,  not_years= distinguish_years(years, years_exist)

        # 获取limit参数
        limit = get_appropriate_limit(limit_param, default_limit = 0)

        # 结果存放的字典
        result_dict = {}

        try:
            with connection.cursor() as cursor:
                # 查询期刊名称
                query_agent = f"SELECT journal FROM agent WHERE id = {id};"
                cursor.execute(query_agent)
                journal_name = cursor.fetchone()[0]

            # 遍历年份并查询每年的主题
            # with connection.cursor() as cursor:
                for year in years_list:
                    theme_dict = {}
                    table_name = f"document_{year}"

                    query_themes = f'SELECT keywords_plus FROM {table_name} WHERE source_title = "{journal_name}";'
                    cursor.execute(query_themes)
                    themes_data = cursor.fetchall()
                    # 构造结果
                    for themes in themes_data:
                        for theme in themes[0].split(';'):
                            theme = theme.strip()
                            if theme:  # 确保 theme 不为空
                                theme_dict[theme] = theme_dict.get(theme, 0) + 1

                    # 移除发文量小于 limit 的作者
                    theme_dict = {theme: count for theme, count in theme_dict.items() if count > limit}

                    # 按值降序排序
                    theme_dict = dict(sorted(theme_dict.items(), key=lambda item: item[1], reverse=True))

                    result_dict[year] = theme_dict

        except Exception as e:
            return Response({"error": f"数据库查询语句执行出错: {str(e)}"}, status=500)

        for year in not_years:
            result_dict[year] = {}
        result_dict = dict(sorted(result_dict.items()))

        return Response(data={
            "code": 2000,
            "id": id,
            "journal_name": journal_name,
            "msg": f"查询成功，返回数据年份：{years_list + not_years}{msg}",
            "data": result_dict
        }, status=200, content_type="application/json")

class JournalTaskRatioView(APIView):
    """
    6. 爬取期刊数
    用于获取某年中 爬取了多少期刊数量，爬取失败的数量，没爬的数量
    返回的数据主要用于 主页中“任务环比”数据的呈现
    --Xwang
    """
    def get(self,request):
        params = request.query_params
        year = params.get('year')

        msg = "查询成功"

        # 获取当前日期和时间
        current_date = datetime.datetime.now()
        if not year:
            msg = "无year参数输入，返回当前年份"
            # 获取当前年份
            year = current_date.year
        else:
            try:
                if not 0 < int(year) <= current_date.year:
                    year = current_date.year
                    msg =  "year参数请求错误，返回今年数据"
            except ValueError:
                return Response({"error": "Wrong type for 'year' parameter"}, status=400)

        query = f"""
                    SELECT 
                        SUM(CASE WHEN last_query_num != -1 THEN 1 ELSE 0 END) AS count_non_minus_one,
                        SUM(CASE WHEN last_query_num = -1 THEN 1 ELSE 0 END) AS count_minus_one
                    FROM 
                        agent_record
                    WHERE 
                        year = {year}
                """

        # 执行查询并获取结果
        with connection.cursor() as cursor:
            if f"document_{year}" not in connection.introspection.table_names(cursor):
                result = [0,0]
            else:
                cursor.execute(query)
                result = cursor.fetchone()

        success = int(result[0])
        fail = int(result[1])
        num = models.Agent.objects.count() - success - fail

        return Response(data={
            "code": 2000,
            "year": year,
            "msg": f"{msg}",
            "爬取成功": success,
            "爬取失败": fail,
            "待爬取": num
        }, status=200, content_type="application/json")

class DataVolumeView(APIView):
    def get(self,request):
        """
        7. 近五年数据量
        No Parameters required
        Return the data volume for the last five years from document_{year}
        --Xwang
        """
        data = {}

        # 获取当前年份
        current_date = datetime.datetime.now()
        year_now = current_date.year
        # years参数列表 近五年（ 例： [2024, 2023, 2022, 2021, 2020] ）
        years = [(year_now - i) for i in range(5)]
        temp_year = []

        with connection.cursor() as cursor:
            for year in years:
                query = f""" select count(*) from document_{year}; """

                if f"document_{year}" not in connection.introspection.table_names(cursor):
                    data[year] = 0
                    temp_year.append(year)
                else:
                    cursor.execute(query)
                    data[year] = cursor.fetchone()[0]

        return Response(data={
            "code": 2000,
            "msg": f"查询近五年数据量成功",
            "data": data,
        }, status=200, content_type="application/json")

class Optimize_DB_View(APIView):
    def get(self,request):
        """
        8. 优化数据库结构
        No Parameters required
        --Xwang
        """
        if not check_isExist_table():
            pass
        years = show_db_table_years()